package com.cntcdt.api.db.dao;

import java.text.SimpleDateFormat;
import java.util.List;

import com.cntcdt.api.db.dao.base.GroupDeviceDayCntDaoBase;
import com.cntcdt.api.db.model.GroupDeviceDayCntModel;
import com.cntcdt.api.db.model.GroupDeviceDayDivideCntModel;
import com.ibm.icu.util.Calendar;

/**
 * 这是cdt_group_device_day_cnt的Dao实现类
 */
public class GroupDeviceDayCntDao extends GroupDeviceDayCntDaoBase   {
    /**
     * GroupDeviceDayCntDao对象
     */
    private static GroupDeviceDayCntDao instance = new GroupDeviceDayCntDao();

    /**
     * 默认构造方法
     */
    private GroupDeviceDayCntDao() {
    }

    /**
     * 获得对象实例
     */
    public static GroupDeviceDayCntDao getInstance() { 
         return instance;
    } 
    
    /**
     * 按昨天的集团数据
     * @param id id
     * @return model
     */
    public GroupDeviceDayCntModel findByYesterday(int groupUserId){
       Calendar calendar = Calendar.getInstance();
       calendar.add(Calendar.DATE, -1);            
       String  yestedayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
       String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE addTime = ? and terminal in (select terminal from cdt_group_demo_device where groupId = ? and opStatus = 0)order by addTime";
       Object[] paramObjs = {yestedayDate,groupUserId};
       List<GroupDeviceDayCntModel> modelList = this.queryModelList(sql, paramObjs);
       if(modelList.size() > 0){
    	   return modelList.get(0);
       }
       return null;
    }
    
    /**
     * 查出本月的集团数据
     * @param id id
     * @return model
     */
    public GroupDeviceDayCntModel findByThisMonth(int groupUserId) {
        Calendar cal_2 = Calendar.getInstance();    
        cal_2.add(Calendar.MONTH, 0);
        cal_2.set(Calendar.DAY_OF_MONTH,1);//设置为1号,当前日期既为本月第一天 
        String firstDay = new SimpleDateFormat("yyyy-MM-dd").format(cal_2.getTime());
    	
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 0);            
        String  todayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE addTime >= ? and addTime <= ? and terminal in (select terminal from cdt_group_demo_device where groupId = ? and opStatus = 0) order by addTime";
       	Object[] paramObjs = {firstDay,todayDate,groupUserId};
       List<GroupDeviceDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       if(modelList.size() > 0){
    	   return modelList.get(0);
       }
       return null;
   } 
    
    /**
     * 查出近7天的集团数据
     * @param id id
     * @return model
     */
    public List<GroupDeviceDayCntModel> findByLastSevenDayData(int groupUserId) {
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -1);            
        String  yesterdayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        
		Calendar calendar2 = Calendar.getInstance();
		calendar2.add(Calendar.DATE, -7);            
        String  lastSevenDayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar2.getTime());
        String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,SUM(borrowRentSum) as borrowRentSum,SUM(sellLineMoneySum) as sellLineMoneySum,SUM(sellCDBSum) as sellCDBSum,sellCDBNum,sellLineNum,presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE addTime >= ? and addTime <= ? and terminal in (select terminal from cdt_group_demo_device where groupId = ? and opStatus = 0) GROUP BY addTime";
    	Object[] paramObjs = {lastSevenDayDate,yesterdayDate,groupUserId};
       List<GroupDeviceDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   }    
    
    /**
     * 按昨天的集团数据
     * @param id id
     * @return model
     */
    public GroupDeviceDayCntModel findByYesterday(String terminal){
       Calendar calendar = Calendar.getInstance();
       calendar.add(Calendar.DATE, -1);            
       String  yestedayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
       String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE addTime = ? and terminal = ? order by addTime";
       Object[] paramObjs = {yestedayDate,terminal};
       List<GroupDeviceDayCntModel> modelList = this.queryModelList(sql, paramObjs);
       if(modelList.size() > 0){
    	   return modelList.get(0);
       }
       return null;
    }
    
    /**
     * 查出本月的集团数据
     * @param id id
     * @return model
     */
    public GroupDeviceDayCntModel findByThisMonth(String terminal) {
        Calendar cal_2 = Calendar.getInstance();    
        cal_2.add(Calendar.MONTH, 0);
        cal_2.set(Calendar.DAY_OF_MONTH,1);//设置为1号,当前日期既为本月第一天 
        String firstDay = new SimpleDateFormat("yyyy-MM-dd").format(cal_2.getTime());
    	
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, 0);            
        String  todayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE addTime >= ? and addTime <= ? and terminal = ? order by addTime";
       	Object[] paramObjs = {firstDay,todayDate,terminal};
       List<GroupDeviceDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       if(modelList.size() > 0){
    	   return modelList.get(0);
       }
       return null;
   } 
    
    /**
     * 查出近7天的集团数据
     * @param id id
     * @return model
     */
    public List<GroupDeviceDayCntModel> findByLastSevenDayData(String terminal) {
		Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DATE, -1);            
        String  yesterdayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar.getTime());
        
		Calendar calendar2 = Calendar.getInstance();
		calendar2.add(Calendar.DATE, -7);            
        String  lastSevenDayDate = new SimpleDateFormat("yyyy-MM-dd").format(calendar2.getTime());
        String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,SUM(borrowRentSum) as borrowRentSum,SUM(sellLineMoneySum) as sellLineMoneySum,SUM(sellCDBSum) as sellCDBSum,sellCDBNum,sellLineNum,presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE addTime >= ? and addTime <= ? and terminal = ? GROUP BY addTime";
    	Object[] paramObjs = {lastSevenDayDate,yesterdayDate,terminal};
       List<GroupDeviceDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   }
    
    
    /**
     * 查出本月的集团数据
     * @param id id
     * @return model
     */
    public List<GroupDeviceDayCntModel> findBySortTimeSum(int groupUserId, long sortTime) {
    	 String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE terminal in (select terminal from cdt_group_demo_device where groupId = ? and opStatus = 0) and sortTime < ? GROUP BY addTime order by sortTime desc limit 20";
		Object[] paramObjs = {groupUserId,sortTime};
       List<GroupDeviceDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   } 
    
    /**
     * 按昨天的集团数据
     * @param id id
     * @return model
     */
    public List<GroupDeviceDayCntModel> findMonthDataBySortTime(int groupUserId,long sortTime){
    	 String sql = "select id,terminal,name,address,addTime,updateTime,sortTime,DATE_FORMAT(addTime,'%Y%m') months,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney from "+TABLE+" where terminal in (select terminal from cdt_group_demo_device where groupId = ? and opStatus = 0) and sortTime < ? group by months order by months desc limit 20";
		Object[] paramObjs = {groupUserId,sortTime};
       List<GroupDeviceDayCntModel> modelList = this.queryModelList(sql, paramObjs);
       return modelList;
    }    
    
    /**
     * 查出本月的集团数据
     * @param id id
     * @return model
     */
    public List<GroupDeviceDayCntModel> findBySortTimeSum(String terminal, long sortTime) {
    	 String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE terminal = ? and sortTime < ? GROUP BY addTime order by sortTime desc limit 20";
		Object[] paramObjs = {terminal,sortTime};
       List<GroupDeviceDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
   } 
    
    /**
     * 按昨天的集团数据
     * @param id id
     * @return model
     */
    public List<GroupDeviceDayCntModel> findMonthDataBySortTime(String terminal,long sortTime){
    	 String sql = "select id,terminal,name,address,addTime,updateTime,sortTime,DATE_FORMAT(addTime,'%Y%m') months,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney from "+TABLE+" where terminal = ? and sortTime < ? group by months order by months desc limit 20";
		Object[] paramObjs = {terminal,sortTime};
       List<GroupDeviceDayCntModel> modelList = this.queryModelList(sql, paramObjs);
       return modelList;
    }  
    
    
    public List<GroupDeviceDayCntModel> findByDateTime(int groupUserId,String time){
    	String sql = "SELECT id,terminal,name,address,addTime,updateTime,sortTime,sum(borrowUserSum) as borrowUserSum,sum(borrowCDBSum) as borrowCDBSum,sum(newUserSum) as newUserSum,sum(borrowRentSum) as borrowRentSum,sum(sellLineMoneySum) as  sellLineMoneySum,sum(sellCDBSum) as sellCDBSum,sum(sellCDBNum) as sellCDBNum,sum(sellLineNum) as sellLineNum,sum(presentLineNum) as presentLineNum,sum(buyCDBGiveBackCnt) as buyCDBGiveBackCnt,sum(buyCDBGiveBackMoney) as buyCDBGiveBackMoney FROM "+TABLE+" WHERE terminal in (select terminal from cdt_group_demo_device where groupId = ? and opStatus = 0) and addTime = ? GROUP BY terminal order by sortTime desc";
		Object[] paramObjs = {groupUserId,time};
       List<GroupDeviceDayCntModel > modelList = this.queryModelList(sql, paramObjs);
       return modelList;
    }

}
